﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using Remind.Model;
using Remind.IDAL;
using Remind.DBUtility;

namespace Remind.AccessDAL
{
    /// <summary>
    /// 用户信息的access数据访问实现层
    /// </summary>
    public class User : IUser
    {
        #region sql语句
        private const string SQL_SELECT_USERS = "SELECT uid,uname,upwd FROM [user]";
        private const string SQL_SELECT_USER_COUNT = "SELECT COUNT(*) FROM [USER]";
        private const string SQL_SELECT_USER_BY_CNAME = "SELECT uid,uname,upwd FROM [user] where uname=@uname";
        private const string SQL_INSERT_USER = "insert into [user](uname,upwd)values(@uname,@upwd)";
        private const string SQL_UPDATE_USER = "update [user] set uname=@uname,upwd=@upwd where uid=@cid";
        private const string SQL_DELETE_USER = "delete from [user] where uid=@uid";
        private const string PARM_USER_UNAME = "@uname";
        private const string PARM_USER_UID = "@uid";
        private const string PARM_USER_UPWD = "@upwd";
        #endregion 

        #region IUser 成员
        /// <summary>
        /// 查询所有用户信息
        /// </summary>
        /// <returns>所有用户信息</returns>
        public IList<UserInfo> GetUsers()
        {
            IList<UserInfo> users = new List<UserInfo>();
            UserInfo user = null;

            using (OleDbDataReader rdr = AccessHelper.ExecuteReader(AccessHelper.ConnectionString, CommandType.Text, SQL_SELECT_USERS, null))
            {
                while (rdr.Read())
                {
                    user = new UserInfo();
                    user.Uid = rdr.GetInt32(0);
                    user.Uname = rdr.GetString(1);
                    user.Upwd = rdr.GetString(2);
                    users.Add(user);
                }
            }
            return users;
        }

        /// <summary>
        /// 根据用户和密码返回信息
        /// </summary>
        /// <param name="uname"></param>
        /// <returns></returns>
        public UserInfo GetUserByUName(string uname)
        {
            UserInfo user = null;

            OleDbParameter parm = new OleDbParameter(PARM_USER_UNAME, OleDbType.VarChar, 16);
            parm.Value = uname;
            using (OleDbDataReader rdr = AccessHelper.ExecuteReader(AccessHelper.ConnectionString, CommandType.Text, SQL_SELECT_USER_BY_CNAME, parm))
            {
                if (rdr.Read())
                {
                    user = new UserInfo();
                    user.Uid = rdr.GetInt32(0);
                    user.Uname = rdr.GetString(1);
                    user.Upwd = rdr.GetString(2);
                }
            }
            return user;
        }

        /// <summary>
        /// 编辑用户信息
        /// </summary>
        /// <param name="userInfo"></param>
        /// <param name="idu"></param>
        public int EditUser(UserInfo userInfo, Idu idu)
        {
            int val = 0;
            //OleDbParameter[] parms = new OleDbParameter[] { 
            //    new OleDbParameter(PARM_USER_UID,OleDbType.Integer),
            //    new OleDbParameter(PARM_USER_UNAME, OleDbType.VarChar, 16),
            //    new OleDbParameter(PARM_USER_UPWD, OleDbType.VarChar, 64)};
            //    parms[0].Value = userInfo.Uid;
            //    parms[1].Value = userInfo.Uname;
            //    parms[2].Value = userInfo.Upwd;
            //存在重复代码，需要优化。但是上面的方法又不能取到对应的参数
            switch (idu)
            {
                case Idu.insert:
                    OleDbParameter[] parms_insert = new OleDbParameter[] { 
                        new OleDbParameter(PARM_USER_UNAME, OleDbType.VarChar, 16),
                        new OleDbParameter(PARM_USER_UPWD, OleDbType.VarChar, 64)};
                    parms_insert[0].Value = userInfo.Uname;
                    parms_insert[1].Value = userInfo.Upwd;
                    val = AccessHelper.ExecuteNonQuery(AccessHelper.ConnectionString, CommandType.Text, SQL_INSERT_USER, parms_insert);
                    break;
                case Idu.update:
                    OleDbParameter[] parms = new OleDbParameter[]{
                        new OleDbParameter(PARM_USER_UID,OleDbType.Integer),
                        new OleDbParameter(PARM_USER_UNAME,OleDbType.VarChar,16),
                        new OleDbParameter(PARM_USER_UPWD,OleDbType.VarChar,16)};
                    parms[0].Value = userInfo.Uid;
                    parms[1].Value = userInfo.Uname;
                    parms[2].Value = userInfo.Upwd;
                    val = AccessHelper.ExecuteNonQuery(AccessHelper.ConnectionString, CommandType.Text, SQL_UPDATE_USER, parms);
                    break;
                case Idu.delete:
                    OleDbParameter userParmsUid = new OleDbParameter(PARM_USER_UID, OleDbType.Integer);
                    userParmsUid.Value = userInfo.Uid;
                    val = AccessHelper.ExecuteNonQuery(AccessHelper.ConnectionString, CommandType.Text, SQL_DELETE_USER, userParmsUid);
                    break;
            }
            return val;
        }
        #endregion

        public int GetUserCount()
        {
            return (int)AccessHelper.ExecuteScalar(AccessHelper.ConnectionString, CommandType.Text, SQL_SELECT_USER_COUNT, null);
        }
    }
}
